#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
验证导入到SQL Server数据库的数据
"""

import pymssql

# 数据库配置
DB_CONFIG = {
    'host': '192.168.0.248',
    'port': 1433,
    'user': 'feng',
    'password': '0400923',
    'database': 'ERPDB4'
}

# 表名
TABLE_NAME = 'response_data'

def connect_to_database():
    """连接到SQL Server数据库"""
    try:
        conn = pymssql.connect(
            server=DB_CONFIG['host'],
            port=DB_CONFIG['port'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            database=DB_CONFIG['database'],
            charset='utf8'
        )
        print(f"成功连接到数据库 {DB_CONFIG['database']}")
        return conn
    except Exception as e:
        print(f"连接数据库失败: {e}")
        return None

def verify_data(conn):
    """验证导入的数据"""
    try:
        cursor = conn.cursor()
        
        # 查询总记录数
        cursor.execute(f"SELECT COUNT(*) FROM {TABLE_NAME}")
        count = cursor.fetchone()[0]
        print(f"表 {TABLE_NAME} 中共有 {count} 条记录")
        
        # 查询前5条记录
        cursor.execute(f"SELECT TOP 5 station_Code_Back, station_Name_Back, workbill_code, cause_name FROM {TABLE_NAME}")
        rows = cursor.fetchall()
        print("\n前5条记录:")
        print("--------------------------------------------------")
        for row in rows:
            print(f"站位代码: {row[0]}, 站位名称: {row[1]}, 工单代码: {row[2]}, 原因: {row[3]}")
        
        # 查询不同原因的数量统计
        cursor.execute(f"SELECT cause_name, COUNT(*) as count FROM {TABLE_NAME} GROUP BY cause_name ORDER BY count DESC")
        causes = cursor.fetchall()
        print("\n原因统计:")
        print("--------------------------------------------------")
        for cause in causes:
            print(f"{cause[0]}: {cause[1]} 条记录")
            
        return True
    except Exception as e:
        print(f"验证数据时出错: {e}")
        return False

def main():
    """主函数"""
    print("开始验证导入的数据...")
    
    # 连接数据库
    conn = connect_to_database()
    if not conn:
        return
    
    try:
        # 验证数据
        if verify_data(conn):
            print("\n数据验证完成!")
        else:
            print("\n数据验证失败!")
    except Exception as e:
        print(f"执行过程中出错: {e}")
    finally:
        if conn:
            conn.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    main()